package db;
import models.*;
import java.sql.*;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */


import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author grizwitz
 */
public class EmployeeDB extends AbstractDb implements EmployeeInterface {

     public EmployeeDB() {
      
    }

  public ArrayList<Employee> getAllEmployees(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }

  public Employee findEmployee(String id, boolean retriveAssociation)
    {   String wClause = "  ssn = '" + id + "'";
        return singleWhere(wClause, retriveAssociation);
    }

   

    private ArrayList<Employee> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Employee> list = new ArrayList<Employee>();

	    String query =  buildQuery(wClause);

            try{ // read the employee from the database

	 	results = this.db.query(query);
		while( results.next() ){
	     	 Employee empObj = new Employee();
		 empObj = buildEmployee(results);
                 list.add(empObj);
		}//end while

            }//slut try
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}


    private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM employee";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}

    private Employee singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Employee empObj = new Employee();
                empObj.setName("");
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the employee from the database

                        results = this.db.query(query);

	 		if( results.next() ){
                            empObj = buildEmployee(results);
                            //assocaition is to be build
			}

		}//end try
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return empObj;
	}

    private Employee buildEmployee(ResultSet results)
      {   Employee empObj = new Employee();
          try{
                empObj.setName(results.getString("emplyee_name"));
                empObj.setId(results.getInt("employee_id"));
                empObj.setUsername(results.getString("employee_username"));

          }
         catch(Exception e)
         {
             System.out.println("error in building the employee object");
         }
         return empObj;
      }



    public int deleteEmployee(String id)
	{
               int rc=-1;

	  	String query="DELETE FROM employee WHERE id = '" +
				id + "'";
                System.out.println(query);
	  	try{ // delete from employee


	 	  	rc = this.db.queryUpdate(query);

  		}//slut try
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in employee db: "+ex);
   	        }
		return(rc);
	}

     public Employee searchEmployeeName(String attValue, boolean retriveAssociation)
    {
        String wClause = "name like '%" + attValue + "%'";
        System.out.println("Search Employee " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }



    public Employee searchEmployeeUsername(String attValue, boolean retriveAssociation)
    {
        String wClause = "username = '" + attValue + "'";
        System.out.println("Search Employee: " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }

    public int updateEmployee(Employee emp)
	{
		Employee empObj  = emp;
		int rc=-1;

		String query="UPDATE employee SET "+
		 	  "name ='"+ empObj.getName()+"', "+
		 	  "username ='"+ empObj.getUsername() + "' " +
		          " WHERE id = '"+ empObj.getId() + "'";
                System.out.println("Update query:" + query);
  		try{ // update employee
	 		rc = this.db.queryUpdate(query);
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in employee db: "+ex);
	  	}
		return(rc);
	}

    public Employee searchEmployeeId(int id, boolean b)
    {
        String wClause = "id = '" + id + "'";
        System.out.println("Search Employee: " + wClause);
        return singleWhere(wClause, b);
    }

    public int insertEmployee(Employee emp)
    {  
           int rc = -1;
	   String query="INSERT INTO employee(name, username, id)  VALUES('"+
	  		emp.getName()  + "','"  +
		    	emp.getUsername()  + "','"  +
                        emp.getId() + "','";
      System.out.println("insert : " + query);
      try{ // insert new employee
            rc = this.db.queryInsert(query);
		}//end try
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in employee db: "+ex);
	   }
	   return(rc);
	}

  public Employee getEmployee(int empid) {
        Employee obj = null;
        try {
            ResultSet rs = this.db.query(""
                    + "SELECT * "
                    + "FROM Employee "
                    + "WHERE employee_id = " + empid);
            if (rs != null && rs.next()) {
                int id = rs.getInt("employee_id");
                String name = rs.getString("employee_name");
                String username = rs.getString("employee_username");
                //int zipcode = rs.getInt("customer_zipcode");
                //int phoneno = rs.getInt("customer_phoneNo");
                obj = new Employee(id, username, name);
            }
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return obj;
    }

   public boolean addEmployee(Employee emp)
        {
        boolean succes = false;
        int r = this.db.queryUpdate("UPDATE Employee SET "+
                "employee_id ='"+ emp.getId()+"', "+
                "employee_name ='"+ emp.getName()+"', "+
                "employee_username ='"+ emp.getUsername() + ";");

        if (r>0) {
            succes = true;
        }
        return succes;
    }






}







